using System;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Management;
using System.Net.Mail;


public partial class SQLAccountDetail : System.Web.UI.Page
{
    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (Convert.ToBoolean(Session["sAdministrator"].ToString()) == false)
            {
                Response.Redirect("~/AccessDenied.aspx");
            }
        }
        BindData();
    }
    private void BindData()
    {
        string sqlQuery = "SELECT * FROM LOGIN_SQL WHERE InstanceName = '" + Request.QueryString["InstanceName"].ToString() + "' AND LoginName = '" + Request.QueryString["LoginName"].ToString() + "'";
        DataSet ds = db.ExecuteDataSet(CommandType.Text, sqlQuery);
        DataRow dr = ds.Tables[0].Rows[0];

        this.txtInstanceName.Text = dr["InstanceName"].ToString();
        this.txtLoginName.Text = dr["LoginName"].ToString();
        this.txtIsExpired.Text = dr["isExpired"].ToString();
        this.txtIsLocked.Text = dr["isLocked"].ToString();
        this.txtLockoutTime.Text = dr["lockouttime"].ToString();
        this.txtIsPolicyChecked.Text = dr["is_policy_checked"].ToString();
        this.txtIsExpirationChecked.Text = dr["is_expiration_checked"].ToString();
        this.txtDefaultDatabase.Text = dr["default_database_name"].ToString();
        this.txtCreateDate.Text = dr["create_date"].ToString();
        this.txtModifyDate.Text = dr["modify_date"].ToString();

        if (dr["isLocked"].ToString() == "True")
        {
            this.txtIsLocked.Font.Bold = true;
            this.txtIsLocked.ForeColor = System.Drawing.Color.Red;
        }

        if (dr["IsExpired"].ToString() == "True")
        {
            this.txtIsExpired.Font.Bold = true;
            this.txtIsExpired.ForeColor = System.Drawing.Color.Red;
        }   

        this.tblPasswordIsLocked.Visible = Convert.ToBoolean(dr["isLocked"]);
        this.tblIsExpired.Visible = Convert.ToBoolean(dr["isExpired"]);
    
    }
    protected void btnUnlockLogin_Click(object sender, EventArgs e)
    {        
        String strTimeDisplay1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1DisplayName'").ToString();
        String strTimeDisplay2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2DisplayName'").ToString();
        String strTimeDisplay3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3DisplayName'").ToString();
        String strTimeDisplay4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4DisplayName'").ToString();

        String strTimeZone1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1TimeZoneID'").ToString();
        String strTimeZone2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2TimeZoneID'").ToString();
        String strTimeZone3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3TimeZoneID'").ToString();
        String strTimeZone4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4TimeZoneID'").ToString();

        DateTimeOffset nowDateTime = DateTimeOffset.Now;
        DateTimeOffset Time1DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone1.ToString()));
        DateTimeOffset Time2DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone2.ToString()));
        DateTimeOffset Time3DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone3.ToString()));
        DateTimeOffset Time4DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone4.ToString()));


        try
        {
            SqlConnection cn1 = new SqlConnection(@"server=" + this.txtInstanceName.Text.ToString() + ";database=master;Integrated Security=SSPI;Pooling=False");
        
            cn1.Open();

            SqlCommand cmdSQL = cn1.CreateCommand();

            if (rdoUnlockType.SelectedItem.Value.ToString() == "0") //UNLOCK (KEEP EXISTING PASSWORD)
            {
                cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_EXPIRATION=OFF";
                cmdSQL.ExecuteNonQuery();
                cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_POLICY=OFF";
                cmdSQL.ExecuteNonQuery();
                cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] Enable";
                cmdSQL.ExecuteNonQuery();

                if (this.chkEnforcePasswordPolicy.Checked)
                {
                    cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_POLICY=ON";
                    cmdSQL.ExecuteNonQuery();
                }
                if (this.chkEnforcePasswordExiration.Checked)
                {
                    cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_EXPIRATION=ON";
                    cmdSQL.ExecuteNonQuery();
                }

                MailMessage msg = new MailMessage();

                msg.To.Add(this.txtEMailAddress.Text.ToString());
                msg.CC.Add(Utility.DBANotificationEMailString());
                msg.Subject = "DBA Alert: SQL Account Unlocked";
                msg.Body = "-----------------------------------------------------" + " \r\n" +
                           "       Alert   : SQL Account Unlocked" + " \r\n" +
                           "-----------------------------------------------------" + " \r\n" +
                           " SQL Instance  : " + this.txtInstanceName.Text.ToString() + " \r\n" +
                           "  SQL Account  : " + this.txtLoginName.Text.ToString() + " \r\n" +
                           " SQL Password  : Not Applicable (use existing password)" + " \r\n" +
                           "     Comments  : " + this.txtComments.Text.ToString() + " \r\n" +
                           " " + " \r\n" +
                           "-----------------------------------------------------" + " \r\n" +
                           " Actioned By   : " + Session["sFullUserName"].ToString() + " \r\n" +
                           "-----------------------------------------------------" + " \r\n" +
                           " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                           "-----------------------------------------------------" + " \r\n" +
                           "END OF MESSAGE" + " \r\n";

                SmtpClient client = new SmtpClient();
                client.Send(msg);

            }

            if (rdoUnlockType.SelectedItem.Value.ToString() == "1") //UNLOCK (WITH NEW PASSWORD)
            {
                cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_EXPIRATION=OFF";
                cmdSQL.ExecuteNonQuery();
                cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_POLICY=OFF";
                cmdSQL.ExecuteNonQuery();
                cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH PASSWORD = '" + this.txtNewPassword.Text.ToString() + "' UNLOCK";
                cmdSQL.ExecuteNonQuery();

                if (this.chkEnforcePasswordPolicy.Checked)
                {
                    cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_POLICY=ON";
                    cmdSQL.ExecuteNonQuery();
                }
                if (this.chkEnforcePasswordExiration.Checked)
                {
                    cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_EXPIRATION=ON";
                    cmdSQL.ExecuteNonQuery();
                }

                MailMessage msg = new MailMessage();

                msg.To.Add(this.txtEMailAddress.Text.ToString());
                msg.CC.Add(Utility.DBANotificationEMailString());
                msg.Subject = "DBA Alert: SQL Account Unlocked";
                msg.Body = "-----------------------------------------------------" + " \r\n" +
                           "       Alert   : SQL Account Unlocked" + " \r\n" +
                           "-----------------------------------------------------" + " \r\n" +
                           " SQL Instance  : " + this.txtInstanceName.Text.ToString() + " \r\n" +
                           "  SQL Account  : " + this.txtLoginName.Text.ToString() + " \r\n" +
                           " SQL Password  : " + this.txtNewPassword.Text.ToString() + " \r\n" +
                           "     Comments  : " + this.txtComments.Text.ToString() + " \r\n" +
                           " " + " \r\n" +
                           "-----------------------------------------------------" + " \r\n" +
                           " Actioned By   : " + Session["sFullUserName"].ToString() + " \r\n" +
                           "-----------------------------------------------------" + " \r\n" +
                           " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                           "-----------------------------------------------------" + " \r\n" +
                           "END OF MESSAGE" + " \r\n";

                SmtpClient client = new SmtpClient();
                client.Send(msg);
            }

            if (rdoUnlockType.SelectedItem.Value.ToString() == "2") //UNLOCK (CHANGE PASSWORD ON NEXT LOGIN)
            {
                cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_EXPIRATION=OFF";
                cmdSQL.ExecuteNonQuery();
                cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_POLICY=OFF";
                cmdSQL.ExecuteNonQuery();
                cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] Enable";
                cmdSQL.ExecuteNonQuery();
                cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_POLICY=ON";
                cmdSQL.ExecuteNonQuery();
                cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_EXPIRATION=ON";
                cmdSQL.ExecuteNonQuery();
                cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH PASSWORD = 'P@ssw0rd' MUST_CHANGE";
                cmdSQL.ExecuteNonQuery();

                MailMessage msg = new MailMessage();

                msg.To.Add(this.txtEMailAddress.Text.ToString());
                msg.CC.Add(Utility.DBANotificationEMailString());
                msg.Subject = "DBA Alert: SQL Account Unlocked";
                msg.Body = "-----------------------------------------------------" + " \r\n" +
                           "       Alert   : SQL Account Unlocked" + " \r\n" +
                           "-----------------------------------------------------" + " \r\n" +
                           " SQL Instance  : " + this.txtInstanceName.Text.ToString() + " \r\n" +
                           "  SQL Account  : " + this.txtLoginName.Text.ToString() + " \r\n" +
                           " SQL Password  : P@ssw0rd" + " \r\n" +
                           "     Comments  : " + this.txtComments.Text.ToString() + " \r\n" +
                           " " + " \r\n" +
                           "-----------------------------------------------------" + " \r\n" +
                           " Actioned By   : " + Session["sFullUserName"].ToString() + " \r\n" +
                           "-----------------------------------------------------" + " \r\n" +
                           " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                           " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                           "-----------------------------------------------------" + " \r\n" +
                           "END OF MESSAGE" + " \r\n";

                SmtpClient client = new SmtpClient();
                client.Send(msg);
            }

            cn1.Close();

            DbCommand dbCommand1 = db.GetSqlStringCommand(@"DELETE FROM LOGIN_SQL WHERE InstanceName ='" + this.txtInstanceName.Text.ToString() + "' and LoginName ='" + this.txtLoginName.Text.ToString() + "'");
            db.ExecuteNonQuery(dbCommand1);

            MessageBox.Show("SQLAccount Unlock: SUCCESSFUL");

            Response.Redirect("~/Dashboard.aspx");
        }
        catch (Exception errSQLAccountUnlock)
        {
            MessageBox.Show("SQLAccount Unlock: UNSUCCESSFUL - " + errSQLAccountUnlock.Message.ToString());
        }     
   
    }
    protected void rdoUnlockType_SelectedIndexChanged(object sender, EventArgs e)
    {    
        if (rdoUnlockType.SelectedItem.Value.ToString() == "0")
        {
            this.txtNewPassword.Visible=false;
            this.lblNewPassword.Visible = false;
        }
        else if(rdoUnlockType.SelectedItem.Value.ToString() == "1")
        {
            this.txtNewPassword.Visible=true;
            this.lblNewPassword.Visible = true;
        }
        else if (rdoUnlockType.SelectedItem.Value.ToString() == "2")
        {
            this.txtNewPassword.Visible = false;
            this.lblNewPassword.Visible = false;

            this.chkEnforcePasswordPolicy.Checked = false;
            this.chkEnforcePasswordExiration.Checked = false;
            this.chkEnforcePasswordPolicy.Visible = false;
            this.chkEnforcePasswordExiration.Visible = false;

        }
    }
    protected void chkEnforcePasswordPolicy_CheckedChanged(object sender, EventArgs e)
    {
        if (this.chkEnforcePasswordPolicy.Checked==true)
        {
            this.chkEnforcePasswordExiration.Visible=true;
        }
        if (this.chkEnforcePasswordPolicy.Checked == false)
        {
            this.chkEnforcePasswordExiration.Checked = false;
            this.chkEnforcePasswordExiration.Visible = false;
        }
    }
    protected void btnUnExpireAccount_Click(object sender, EventArgs e)
    {
        String strTimeDisplay1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1DisplayName'").ToString();
        String strTimeDisplay2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2DisplayName'").ToString();
        String strTimeDisplay3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3DisplayName'").ToString();
        String strTimeDisplay4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4DisplayName'").ToString();

        String strTimeZone1 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time1TimeZoneID'").ToString();
        String strTimeZone2 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time2TimeZoneID'").ToString();
        String strTimeZone3 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time3TimeZoneID'").ToString();
        String strTimeZone4 = db.ExecuteScalar(CommandType.Text, "SELECT ConfigValue FROM Config Where ConfigName ='Time4TimeZoneID'").ToString();

        DateTimeOffset nowDateTime = DateTimeOffset.Now;
        DateTimeOffset Time1DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone1.ToString()));
        DateTimeOffset Time2DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone2.ToString()));
        DateTimeOffset Time3DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone3.ToString()));
        DateTimeOffset Time4DateTime = TimeZoneInfo.ConvertTime(nowDateTime, TimeZoneInfo.FindSystemTimeZoneById(strTimeZone4.ToString()));

        try
        {
            SqlConnection cn1 = new SqlConnection(@"server=" + this.txtInstanceName.Text.ToString() + ";database=master;Integrated Security=SSPI;Pooling=False");
            cn1.Open();

            SqlCommand cmdSQL = cn1.CreateCommand();

            cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_EXPIRATION=OFF";
            cmdSQL.ExecuteNonQuery();
            cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_POLICY=OFF";
            cmdSQL.ExecuteNonQuery();
            cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] Enable";
            cmdSQL.ExecuteNonQuery();
            cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_POLICY=ON";
            cmdSQL.ExecuteNonQuery();
            cmdSQL.CommandText = @"ALTER LOGIN [" + this.txtLoginName.Text.ToString() + "] WITH CHECK_EXPIRATION=ON";
            cmdSQL.ExecuteNonQuery();

            MailMessage msg = new MailMessage();

            msg.To.Add(Utility.DBANotificationEMailString());
            msg.Subject = "DBA Alert: SQL Account Un-Expired";
            msg.Body = "-----------------------------------------------------" + " \r\n" +
                       "       Alert   : SQL Account Un-Expired" + " \r\n" +
                       "-----------------------------------------------------" + " \r\n" +
                       " SQL Instance  : " + this.txtInstanceName.Text.ToString() + " \r\n" +
                       "  SQL Account  : " + this.txtLoginName.Text.ToString() + " \r\n" +
                       " " + " \r\n" +
                       "-----------------------------------------------------" + " \r\n" +
                       " Actioned By   : " + Session["sFullUserName"].ToString() + " \r\n" +
                       "-----------------------------------------------------" + " \r\n" +
                       " " + strTimeDisplay1.ToString().PadRight(13) + " : " + Time1DateTime.DateTime.ToString() + " \r\n" +
                       " " + strTimeDisplay2.ToString().PadRight(13) + " : " + Time2DateTime.DateTime.ToString() + " \r\n" +
                       " " + strTimeDisplay3.ToString().PadRight(13) + " : " + Time3DateTime.DateTime.ToString() + " \r\n" +
                       " " + strTimeDisplay4.ToString().PadRight(13) + " : " + Time4DateTime.DateTime.ToString() + " \r\n" +
                       "-----------------------------------------------------" + " \r\n" +
                       "END OF MESSAGE" + " \r\n";

            SmtpClient client = new SmtpClient();
            client.Send(msg);

            cn1.Close();

            DbCommand dbCommand1 = db.GetSqlStringCommand(@"DELETE FROM LOGIN_SQL WHERE InstanceName ='" + this.txtInstanceName.Text.ToString() + "' and LoginName ='" + this.txtLoginName.Text.ToString() + "'");
            db.ExecuteNonQuery(dbCommand1);

            MessageBox.Show("SQLAccount UnExpire: SUCCESSFUL");

            Response.Redirect("~/Dashboard.aspx");

        }
        catch (Exception errSQLAccountUnExpire)
        {
            MessageBox.Show("SQLAccount UnExpire: UNSUCCESSFUL - " + errSQLAccountUnExpire.Message.ToString());
        } 
    }
}
